Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


The initialization parameter DB_BLOCK_BUFFERS specifies the number of database block buffers configured in the system. The total amount of space utilized by these buffers is calculated as follows:

Buffer Size = DB_BLOCK_BUFFERS * DB_BLOCK_SIZE


CAUTION:  Be careful not to configure DB_BLOCK_BUFFERS to use more memory than the system has allocated. Doing so can cause swapping or paging to occur in some systems or a failure of the Oracle instance to start up in other systems.

You can obtain an estimate of additional cache hits that would be achieved with more database block buffers through the virtual table X$KCBRBH. When this table is enabled through the Oracle initialization parameter DB_BLOCK_LRU_EXTENDED_STATISTICS, it contains estimates of the performance of a larger buffer cache.

The parameter DB_BLOCK_LRU_EXTENDED_STATISTICS specifies the number of rows in the X$KCBRBH table. Each row contains the estimated cache hits obtained by those additional buffers. The table X$KCBRBH contains the following columns:

  INDX: The value is 1 less than the number of buffers that would be added.
  COUNT: The value is the estimated number of additional cache hits that would be obtained by adding INDX + 1 buffers.

Be sure to run your application while you are gathering these statistics. Doing so gives you a better representation of how your particular system will react with more database block buffers.

Because the data files and the database block buffers make up the majority of the I/Os in the system, it is important to have a sufficiently large buffer cache. Don’t increase your database block buffers at the expense of the shared pool. Although the shared pool is not as large or as heavily used, it serves a critical purpose in the execution of SQL statements.

Any time you make a significant change in DB_BLOCK_BUFFERS—or any Oracle parameter—go back and check the OS for different I/O rates. Also check within Oracle to see whether the cache-hit rates have changed, as well as the I/O rates.

Tuning the I/O Subsystem

I/O is probably one of the most common problems facing Oracle users. In many cases, the performance of the system is entirely limited by disk I/O. In some cases, the system actually becomes idle waiting for disk requests to complete. We say that these systems are I/O bound or disk bound.

As you see in Chapter 14, “Advanced Disk I/O Concepts,” disks have certain inherent limitations that cannot be overcome. Therefore, the way to deal with disk I/O issues is to understand the limitations of the disks and design your system with these limitations in mind. Knowing the performance characteristics of your disks can help you in the design stage.

Optimizing your system for I/O should happen during the design stage. As you see in Part III, “Configuring the System,” different types of systems have different I/O patterns and require different I/O designs. Once the system is built, you should first tune for memory and then tune for disk I/O. The reason you tune in this order is to make sure that you are not dealing with excessive cache misses, which cause additional I/Os.

The strategy for tuning disk I/O is to keep all drives within their physical limits. Doing so reduces queuing time—and thus increases performance. In your system, you may find that some disks process many more I/Os per second than other disks. These disks are called “hot spots.” Try to reduce hot spots whenever possible. Hot spots occur whenever there is a lot of contention on a single disk or set of disks.

Understanding Disk Contention

Disk contention occurs whenever the physical limitations of a disk drive are reached and other processes have to wait. Disk drives are mechanical and have a physical limitation on both disk seeks per second and throughput. If you exceed these limitations, you have no choice but to wait.

You can find out if you are exceeding these limits both through Oracle’s file I/O statistics and through operating system statistics. This chapter looks at the Oracle statistics; Chapter 12, “Operating System-Specific Tuning,” looks at the operating system statistics for some popular systems.

Although the Oracle statistics give you an accurate picture of how many I/Os have taken place for a particular data file, they may not accurately represent the entire disk because other activity outside of Oracle may be incurring disk I/Os. Remember that you must correlate the Oracle data file to the physical disk on which it resides.

Information about disk accesses is kept in the dynamic performance table V$FILESTAT. Important information in this table is listed in the following columns:

  PHYRDS: The number of physical reads done to the data file.
  PHYWRTS: The number of physical writes done to the data file.

The information in V$FILESTAT is referenced by file number. The dynamic performance table V$DATAFILE contains a reference to this number as well as other useful information such as this:

  NAME: The name of the data file.
  STATUS: The type of file and its current status.
  BYTES: The size of the data file.

Together, the V$FILESTAT and V$DATAFILE tables can give you an idea of the I/O usage of your data files. Use the following query to get this information:

SQL> SELECT substr(name,1,40), phyrds, phywrts, status, bytes
  2  FROM v$datafile df, v$filestat fs
  3  WHERE df.file# = fs.file#;

SUBSTR(NAME,1,40)                           PHYRDS   PHYWRTS STATUS      BYTES
----------------------------------------  --------  -------- ------   --------
C:\UTIL\ORAWIN\DBS\wdbsys.ora                  221         7 SYSTEM   10485760
C:\UTIL\ORAWIN\DBS\wdbuser.ora                   0         0 ONLINE    3145728
C:\UTIL\ORAWIN\DBS\wdbrbs.ora                    2         0 ONLINE    3145728
C:\UTIL\ORAWIN\DBS\wdbtemp.ora                   0         0 ONLINE    2097152

The total I/O for each data file is the sum of the physical reads and physical writes. It is important to make sure that these I/Os don’t exceed the physical limitations of any one disk. I/O throughput problems to one disk may slow down the entire system depending on what data is on that disk. It is particularly important to make sure that I/O rates are not exceeded on the disk drives.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.